﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using Model;

namespace Data
{
    public class ConsumoDataAccess
    {
        private SQLiteConnection con = DataBaseHelper.GetConnection("BDHostal.sqlite");
        private DataTable dt = new DataTable();
        private DataSet dataSet = new DataSet();

        public void NuevoConsumo(consumo c)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "INSERT INTO consumo(nhabitacion,idproducto,fecha,cantidad,activo) VALUES ('" + c.hab + "','" + c.prod + "','" + c.fecha + "','" + c.cantidad + "','si')";
                    cmd.ExecuteNonQuery();

                }
                catch (SQLiteException ex)
                {

                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Registrando Habitacion.");
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        public void cancelarConsumo(string numeroHabitacion)
        {
            string cmd = "UPDATE consumo SET activo='no' WHERE nHabitacion = '" + numeroHabitacion + "' AND activo='si'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex) { }
                finally
                {
                    con.Close();
                }
            }
        }

        public bool existeHabitacion(string numeroHabitacionAntigua)
        {
            bool resultado;
            string cmd = "SELECT nHabitacion FROM consumo WHERE nHabitacion='" + numeroHabitacionAntigua + "' AND activo='si'";
            {
                try
                {
                    con.Open();
                    DataSet dataSet = new DataSet();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    DataTable dt = new DataTable();
                    dt = dataSet.Tables[0];

                    if (dt.Rows[0].Equals(null))
                        resultado = false;
                    else
                        resultado = true;
                }
                catch (SystemException ex)
                {
                    resultado = false;
                }
                finally
                {
                    con.Dispose();
                    con.Close();
                }
            }
            return resultado;
        }

        public void actualizarConsumo(string numeroHabitacionAntigua, string numeroHabitacion)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "UPDATE consumo SET nHabitacion='" + numeroHabitacion + "' WHERE nHabitacion='" + numeroHabitacionAntigua + "'";
                    cmd.ExecuteNonQuery();
                }
                catch (SQLiteException ex){}
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        public DataTable buscarConsumo(string habitacion)
        
        {
            string cmd = "SELECT p.nombre,p.precio,c.cantidad FROM consumo c,producto p WHERE c.nhabitacion=" + habitacion + " AND c.idproducto=p.idproducto AND c.activo='si'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }


        public string total(string habitacion)
        {
            string cmd = "SELECT SUM(precio*cantidad)AS total FROM consumo c,producto p WHERE c.nhabitacion=" + habitacion + " AND c.idproducto=p.idproducto AND c.activo='si'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt.Rows[0][0].ToString();
        }

        public DataTable reporteConsumo()
        {
            string cmd = "SELECT p.nombre,c.cantidad,c.fecha FROM consumo c,producto p WHERE c.idproducto=p.idproducto AND c.activo='no'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        
        }
    }
}
